<?php
/**
 * Query Object
 *
 * @category   Epic
 * @package    Epic_Db
 * @author     Nguyễn Đình nhật <nguyendnhat@gmail.com>
 */
class Epic_Db_Select 
{    
    const FROM           = 'from';
    const WHERE          = 'where';
    const ORDER          = 'order';
	const GROUPBY           = 'GROUP BY';
    const LIMIT_COUNT    = 'limitcount';
    const LIMIT_OFFSET   = 'limitoffset';
    
	/**
     * Kết nối các thành phần câu truy vấn SELECT.
     *
     * @var array
     */
    protected $_parts = array();
    
    /**
     * Mảng khởi tạo giá trị ban đầu cho mảng $_parts.
     * @var array
     */
    protected static $_partsInit = array(
        self::FROM         => array(),
        self::WHERE        => array(),
        self::ORDER        => array(),
		self::GROUPBY        => array(),
        self::LIMIT_COUNT  => null,
        self::LIMIT_OFFSET => null,
    );
    
    /**
     * Khởi tạo giá trị ban đầu cho mảng $_parts.
     */
    public function __construct() 
    {
    	$this->_parts = self::$_partsInit;
    }
     
    /**
     * Lựa chọn bảng truy vấn
     *
     * @param  string $table
     * @return Epic_Db_Select
     */
    public function from($table)
    {
        $this->_parts[self::FROM] = array('fields'        => 'm.*',
          							  	  'from'		  => $table . ' AS m',
		              				      'joinCondition' => '');
        return $this;
    }

    /**
     * Kết nối thêm bảng
     *
     * @param  string $table
     * @param  string $on
     * @param  array $cond
     * @return Epic_Db_Select
     */
    public function join($table, $on, $cond)
    {
    	foreach ($cond as $key => $value) {
    		$this->_parts[self::FROM]['fields'] = $this->_parts['from']['fields'] . ', '
    										. $value . ' AS ' . $key; 
    	}
    	$this->_parts[self::FROM]['joinCondition'] .= ' INNER JOIN ' . $table . ' ON ' . $on; 
    	return $this;       
    }
    
    /**
     * Kết nối thêm bảng
     *
     * @param  string $table
     * @param  string $on
     * @param  array $cond
     * @return Epic_Db_Select
     */
    public function leftJoin($table, $on, $cond)
    {
    	foreach ($cond as $key => $value) {
    		$this->_parts[self::FROM]['fields'] = $this->_parts['from']['fields'] . ', '
    										. $value . ' AS ' . $key; 
    	}
    	$this->_parts[self::FROM]['joinCondition'] .= ' LEFT JOIN ' . $table . ' ON ' . $on; 
    	return $this;       
    }

    
    /**
     * Indicate fileds received
     *
     * @param  string|array $fields
     * @return Epic_Db_Select
     */
    public function field($fields) 
    {
    	if(is_array($fields)) {
    		$fields = implode(', ', $fields);
    	}
    	$this->_parts[self::FROM]['fields'] = 'm.' . $fields;
    	
    	return $this;
    }
    
    /**
     * Thêm lựa chọn thứ tự trả về cho truy vấn
     *
     * @param  string $field
     * @param  string $direction
     * @return Epic_Db_Select
     */
    public function order($field, $direction = 'DESC')
    {
    	$direction = (strtoupper($direction) == 'ASC') ? 'ASC' : 'DESC';
    	$this->_parts[self::ORDER] = $field . ' ' . $direction;
        return $this;
    }
    
    /**
     * Filter the data indicated by conditions
     *
     * @param string $field
     * @param array $condition
     * @return Epic_Db_Select
     */
    public function where($field, $condition)
    {
        if (isset($condition['eq'])) {
        	if ($this->_parts[self::WHERE])
        		$this->_parts[self::WHERE] .= ' AND ' . $field . ' = ' . $condition['eq'];
        	else
          		$this->_parts[self::WHERE] = $field . ' = ' . $condition['eq'];      		
        } else if (isset($condition['like'])) {
			if ($this->_parts[self::WHERE])
				$this->_parts[self::WHERE] .= ' AND '.$field . ' LIKE ' . $condition['like'];
			else 
				$this->_parts[self::WHERE] = $field . ' LIKE ' . $condition['like'];      		
		} else if (isset($condition['in'])) {
			if ($this->_parts[self::WHERE])
				$this->_parts[self::WHERE] .= ' AND '.$field . ' in ' . $condition['in'];
			else 
				$this->_parts[self::WHERE] = $field . ' in ' . $condition['in'];      		
		}
        return $this;
    }
    
    /**
     * Thiết lập giới hạn số kết quả trả về.
     *
     * @param int $count OPTIONAL số hàng trả về.
     * @param int $offset OPTIONAL bắt đầu trả lại từ hàng thứ bao nhiêu.
     * @return Epic_Db_Select
     */
    public function limit($count = null, $offset = 0)
    {
        $this->_parts[self::LIMIT_COUNT]  = (int) $count;
        $this->_parts[self::LIMIT_OFFSET] = (int) $offset;
        return $this;
    }
     
    /**
     * Trả lại thực thi một câu truy vấn SELECT
     * 
     * @return string
     */
	public function select()
    {
        $sql = 'SELECT ' . $this->_parts[self::FROM]['fields'] 
        	 . ' FROM ' . $this->_parts[self::FROM]['from']
        	 . ' ' .$this->_parts[self::FROM]['joinCondition']
        	 . (($this->_parts[self::WHERE]) ? ' WHERE ' . $this->_parts[self::WHERE] : '')
        	 . (($this->_parts[self::ORDER]) ? ' ORDER BY ' . $this->_parts[self::ORDER] : '')
			 . (($this->_parts[self::GROUPBY]) ? ' GROUP BY ' . $this->_parts[self::GROUPBY] : '')
        	 . (($this->_parts[self::LIMIT_COUNT]) ? ' LIMIT ' 
        	 . $this->_parts[self::LIMIT_OFFSET]  . ', ' . $this->_parts[self::LIMIT_COUNT] : '');
        return $sql;
    }
    
	/**
     * Nhóm theo tên
     *
     * @param  string $fields
     * @return Epic_Db_Select
     */
    public function groupBy($fields)
    {
    	foreach ($fields as $key => $value) {
			if ($this->_parts[self::GROUPBY])
				$this->_parts[self::GROUPBY] .=",". $value; 
			else 
				$this->_parts[self::GROUPBY] = $value; 
    	}
    	return $this;       
    }
	
    /**
     * Trả lại thực thi một câu truy vấn SELECT COUNT(*)
     * 
     * @return string
     */
	public function count()
    {
        $sql = 'SELECT COUNT(*) AS cnt' 
        	 . ' FROM ' . $this->_parts[self::FROM]['from']
        	 . ' ' .$this->_parts[self::FROM]['joinCondition']
        	 . (($this->_parts[self::WHERE]) ? ' WHERE ' . $this->_parts[self::WHERE] : '')
        	 . (($this->_parts[self::ORDER]) ? ' ORDER BY ' . $this->_parts[self::ORDER] : '')
        	 . (($this->_parts[self::LIMIT_COUNT]) ? ' LIMIT ' 
        	 . $this->_parts[self::LIMIT_OFFSET]  . ', ' . $this->_parts[self::LIMIT_COUNT] : '');
        return $sql;
    }

    /**
     * Trả lại truy vấn SELECT
     * 
     * @return string
     */
	public function __tostring()
    {
		return $this->select();
    }
    
}